﻿using Model.Entites.Base;
using SqlSugar;
using System.Linq.Expressions;

namespace Utils.Extensions
{
    public static class DbExtensions
    {
        #region 新增

        /// <summary>
        /// 批量添加实体
        /// </summary>
        /// <param name="entities">要添加的实体列表</param>
        /// <returns>受影响的行数</returns>
        public static async Task<int> Add<T>(this ISqlSugarClient Db, List<T> entities) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.Insertable(entities).ExecuteCommandAsync());
        }

        /// <summary>
        /// 添加单个实体
        /// </summary>
        /// <param name="entity">要添加的实体</param>
        /// <returns>受影响的行数</returns>
        public static async Task<int> Add<T>(this ISqlSugarClient Db, T entity) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.Insertable(entity).ExecuteCommandAsync());
        }

        /// <summary>
        /// 添加实体并返回新增的实体对象
        /// </summary>
        /// <param name="entity">要添加的实体</param>
        /// <returns>新增的实体对象</returns>
        public static async Task<T> AddREntity<T>(this ISqlSugarClient Db, T entity) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.Insertable(entity).ExecuteReturnEntityAsync());
        }

        /// <summary>
        /// 添加实体并返回自增ID
        /// </summary>
        /// <param name="entity">要添加的实体</param>
        /// <returns>自增ID</returns>
        public static async Task<int> AddRId<T>(this ISqlSugarClient Db, T entity) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.Insertable(entity).ExecuteReturnIdentityAsync());
        }

        #endregion 新增

        #region 删除

        /// <summary>
        /// 根据ID删除实体
        /// </summary>
        /// <param name="id">实体的ID</param>
        /// <returns>如果删除成功返回true，否则返回false</returns>
        public static async Task<bool> Delete<T>(this ISqlSugarClient Db, int id) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.Deleteable<T>().In(id).ExecuteCommandAsync()) > 0;
        }

        /// <summary>
        /// 根据ID列表删除实体
        /// </summary>
        /// <param name="ids">实体的ID列表</param>
        /// <returns>如果删除成功返回true，否则返回false</returns>
        public static async Task<bool> Delete<T>(this ISqlSugarClient Db, List<int> ids) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.Deleteable<T>().In(primaryKeyValues: ids).ExecuteCommandAsync()) > 0;
        }

        /// <summary>
        /// 根据条件表达式删除实体
        /// </summary>
        /// <param name="whereExpression">删除条件表达式</param>
        /// <returns>如果删除成功返回true，否则返回false</returns>
        public static async Task<bool> Delete<T>(this ISqlSugarClient Db, Expression<Func<T, bool>> whereExpression) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.Deleteable<T>().Where(whereExpression).ExecuteCommandAsync()) > 0;
        }

        #endregion 删除

        #region 查询

        /// <summary>
        /// 根据ID获取单个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Db"></param>
        /// <param name="id"></param>
        /// <param name="isCache"></param>
        /// <param name="cacheSecond"></param>
        /// <returns></returns>
        public static async Task<T> Get<T>(this ISqlSugarClient Db, int id) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.Queryable<T>().Where(u => !u.IsDeleted).InSingleAsync(id));
        }

        /// <summary>
        /// 根据条件表达式获取单个实体
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public static async Task<T> Get<T>(this ISqlSugarClient Db, Expression<Func<T, bool>> whereExpression = null) where T : ModelBase, new()
        {
            var condition = Db.Queryable<T>().Where(u => !u.IsDeleted).WhereIF(whereExpression != null, whereExpression);
            return await Trycatch(() => condition.FirstAsync());
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public static async Task<int> Count<T>(this ISqlSugarClient Db, Expression<Func<T, bool>> whereExpression = null) where T : ModelBase, new()
        {
            return await Trycatch(() =>
            Db.Queryable<T>()
            .Where(u => !u.IsDeleted)
            .WhereIF(whereExpression != null, whereExpression)
            .CountAsync());
        }

        /// <summary>
        /// 根据条件表达式获取实体列表
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public static async Task<List<T>> GetList<T>(this ISqlSugarClient Db, Expression<Func<T, bool>> whereExpression = null) where T : ModelBase, new()
        {
            var condition = Db.Queryable<T>().Where(u => !u.IsDeleted).WhereIF(whereExpression != null, whereExpression);

            return await Trycatch(() => condition.ToListAsync());
        }

        /// <summary>
        /// 根据SQL语句获取实体列表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static async Task<List<T>> GetList<T>(this ISqlSugarClient Db, string sql) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.SqlQueryable<T>(sql).ToListAsync());
        }

        /// <summary>
        /// 是否存在这条记录
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public static async Task<bool> Any<T>(this ISqlSugarClient Db, Expression<Func<T, bool>> whereExpression = null) where T : ModelBase, new()
        {
            return await Trycatch(() =>
            Db.Queryable<T>()
            .Where(u => !u.IsDeleted)
            .WhereIF(whereExpression != null, whereExpression)
            .AnyAsync());
        }

        /// <summary>
        /// 根据ID判断记录是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static async Task<bool> Any<T>(this ISqlSugarClient Db, int id) where T : ModelBase, new()
        {
            return await Db.Any<T>(u => u.Id == id);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="expression"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderBy"></param>
        /// <param name="orderByType"></param>
        /// <returns></returns>
        public static async Task<(List<T>, int, int)> Page<T>(this ISqlSugarClient Db, Expression<Func<T, bool>> expression = null, int pageIndex = 1, int pageSize = 15,
            Expression<Func<T, object>> orderBy = null, OrderByType orderByType = OrderByType.Asc) where T : ModelBase, new()
        {
            RefAsync<int> totalCount = 0;

            var condition = Db.Queryable<T>()
            .Where(u => !u.IsDeleted)
            .WhereIF(expression != null, expression)
            .OrderByIF(orderBy != null, orderBy, orderByType);
            var _cachekey = $"Page_{typeof(T).Name}_{condition}_{pageIndex}_{pageSize}_{orderByType}";

            List<T> list = await Trycatch(() => condition.ToPageListAsync(pageIndex, pageSize, totalCount.Value));
            int pageCount = Math.Ceiling(totalCount.Value.ToDecimal() / pageSize.ToDecimal()).ToInt();
            return (list, totalCount.Value, pageCount);
        }

        /// <summary>
        /// 根据SQL分页查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderBy"></param>
        /// <param name="orderByType"></param>
        /// <returns></returns>
        public static async Task<(List<T>, int, int)> Page<T>(this ISqlSugarClient Db, string sql = null, int pageIndex = 1, int pageSize = 15,
            Expression<Func<T, object>> orderBy = null, OrderByType orderByType = OrderByType.Asc) where T : ModelBase, new()
        {
            RefAsync<int> totalCount = 0;

            var condition = Db.SqlQueryable<T>(sql).OrderByIF(orderBy != null, orderBy, orderByType);
            var _cachekey = $"Page_{typeof(T).Name}_{condition}_{pageIndex}_{pageSize}_{orderByType}";

            List<T> list = await Trycatch(() => condition.ToPageListAsync(pageIndex, pageSize, totalCount.Value));
            int pageCount = Math.Ceiling(totalCount.Value.ToDecimal() / pageSize.ToDecimal()).ToInt();
            return (list, totalCount.Value, pageCount);
        }

        #endregion 查询

        #region 查询2

        /// <summary>
        /// 根据ID获取单个实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static async Task<V> Get<T, V>(this ISqlSugarClient Db, int id) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.Queryable<T>().Where(u => !u.IsDeleted).Select<V>().InSingleAsync(id));
        }

        /// <summary>
        /// 根据条件表达式获取单个实体
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public static async Task<V> Get<T, V>(this ISqlSugarClient Db, Expression<Func<T, bool>> whereExpression = null) where T : ModelBase, new()
        {
            var condition = Db.Queryable<T>().Where(u => !u.IsDeleted).WhereIF(whereExpression != null, whereExpression).Select<V>();

            return await Trycatch(() => condition.FirstAsync());
        }

        /// <summary>
        /// 根据条件表达式获取实体列表
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public static async Task<List<V>> GetList<T, V>(this ISqlSugarClient Db, Expression<Func<T, bool>> whereExpression = null) where T : ModelBase, new()
        {
            var condition = Db.Queryable<T>().Where(u => !u.IsDeleted).WhereIF(whereExpression != null, whereExpression).Select<V>();
            return await Trycatch(() => condition.ToListAsync());
        }

        /// <summary>
        /// 根据SQL语句获取实体列表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static async Task<List<V>> GetList<T, V>(this ISqlSugarClient Db, string sql) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.SqlQueryable<T>(sql).Select<V>().ToListAsync());
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="expression"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderBy"></param>
        /// <param name="orderByType"></param>
        /// <returns></returns>
        public static async Task<(List<V>, int, int)> Page<T, V>(this ISqlSugarClient Db, Expression<Func<T, bool>> expression = null, int pageIndex = 1, int pageSize = 15,
            Expression<Func<T, object>> orderBy = null, OrderByType orderByType = OrderByType.Asc) where T : ModelBase, new()
        {
            RefAsync<int> totalCount = 0;

            var condition = Db.Queryable<T>()
            .Where(u => !u.IsDeleted)
            .WhereIF(expression != null, expression)
            .OrderByIF(orderBy != null, orderBy, orderByType)
            .Select<V>();
            List<V> list = await Trycatch(() => condition.ToPageListAsync(pageIndex, pageSize, totalCount.Value));
            int pageCount = Math.Ceiling(totalCount.Value.ToDecimal() / pageSize.ToDecimal()).ToInt();
            return (list, totalCount.Value, pageCount);
        }

        /// <summary>
        /// 根据SQL分页查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderBy"></param>
        /// <param name="orderByType"></param>
        /// <returns></returns>
        public static async Task<(List<V>, int, int)> Page<T, V>(this ISqlSugarClient Db, string sql = null, int pageIndex = 1, int pageSize = 15,
            Expression<Func<T, object>> orderBy = null, OrderByType orderByType = OrderByType.Asc) where T : ModelBase, new()
        {
            RefAsync<int> totalCount = 0;

            var condition = Db.SqlQueryable<T>(sql).OrderByIF(orderBy != null, orderBy, orderByType).Select<V>();
            var _cachekey = $"Page_{typeof(T).Name}_{condition}_{pageIndex}_{pageSize}_{orderByType}/{typeof(V).Name}";

            List<V> list = await Trycatch(() => condition.ToPageListAsync(pageIndex, pageSize, totalCount.Value));
            int pageCount = Math.Ceiling(totalCount.Value.ToDecimal() / pageSize.ToDecimal()).ToInt();
            return (list, totalCount.Value, pageCount);
        }

        #endregion 查询2

        #region 更新

        /// <summary>
        /// 更新实体
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static async Task<int> Update<T>(this ISqlSugarClient Db, T entity) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.Updateable(entity).ExecuteCommandAsync());
        }

        /// <summary>
        /// 只更新不为null的字段
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static async Task<int> UpdateNotNull<T>(this ISqlSugarClient Db, T entity) where T : ModelBase, new()
        {
            //var updateObj = Db.Updateable(entity).Where(u => u.Id == entity.Id);

            //foreach (var property in typeof(T).GetProperties())
            //{
            //    var value = property.GetValue(entity);
            //    if (value != null)
            //    {
            //        updateObj = updateObj.SetColumns(it => property.Name == property.Name, value);
            //    }
            //}

            //return await Trycatch(() => updateObj.ExecuteCommandAsync());

            return await Trycatch(() => Db.Updateable(entity).IgnoreColumns(ignoreAllNullColumns: true, ignoreAllDefaultValue: true).ExecuteCommandAsync());
        }

        /// <summary>
        /// 更新实体并指定更新与忽略的列
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="columns"></param>
        /// <param name="igcolumns"></param>
        /// <returns></returns>
        public static async Task<int> Update<T>(this ISqlSugarClient Db, T entity, Expression<Func<T, object>> columns = null, Expression<Func<T, object>> igcolumns = null) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.Updateable(entity).UpdateColumnsIF(columns != null, columns).IgnoreColumnsIF(igcolumns != null, igcolumns).ExecuteCommandAsync());
        }

        /// <summary>
        /// 更新实体并指定更新与忽略的列
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="columns"></param>
        /// <param name="igcolumns"></param>
        /// <returns></returns>
        public static async Task<int> Update<T>(this ISqlSugarClient Db, Expression<Func<T, bool>> expression = null, Expression<Func<T, T>> columns = null, Expression<Func<T, object>> igcolumns = null) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.Updateable<T>().SetColumnsIF(columns != null, columns).IgnoreColumnsIF(igcolumns != null, igcolumns).WhereIF(expression != null, expression).ExecuteCommandAsync());
        }

        /// <summary>
        /// 更新实体并指定更新与忽略的列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="columns"></param>
        /// <param name="igcolumns"></param>
        /// <returns></returns>
        public static async Task<int> Update<T>(this ISqlSugarClient Db, int id, Expression<Func<T, T>> columns = null, Expression<Func<T, object>> igcolumns = null) where T : ModelBase, new()
        {
            return await Db.Update(u => u.Id == id, columns, igcolumns);
        }

        /// <summary>
        /// 批量更新实体并指定更新与忽略的列
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="columns"></param>
        /// <param name="igcolumns"></param>
        /// <returns></returns>
        public static async Task<int> Update<T>(this ISqlSugarClient Db, List<T> entity, Expression<Func<T, object>> columns = null, Expression<Func<T, object>> igcolumns = null) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.Updateable(entity).UpdateColumnsIF(columns != null, columns).IgnoreColumnsIF(igcolumns != null, igcolumns).ExecuteCommandAsync());
        }

        #endregion 更新

        #region 软删除

        /// <summary>
        /// 软删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Db"></param>
        /// <param name="id"></param>
        /// <param name="DeleterId"></param>
        /// <returns></returns>
        public static async Task<int> SoftDelete<T>(this ISqlSugarClient Db, int id, int DeleterId = 0) where T : ModelBase, new()
        {
            return await Db.SoftDelete<T>(u => u.Id == id, DeleterId);
        }

        /// <summary>
        /// 软删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Db"></param>
        /// <param name="ids"></param>
        /// <param name="DeleterId"></param>
        /// <returns></returns>
        public static async Task<int> SoftDelete<T>(this ISqlSugarClient Db, List<int> ids, int DeleterId = 0) where T : ModelBase, new()
        {
            return await Db.SoftDelete<T>(u => ids.Contains(u.Id), DeleterId);
        }

        /// <summary>
        /// 软删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Db"></param>
        /// <param name="wherexp"></param>
        /// <param name="DeleterId"></param>
        /// <returns></returns>
        public static async Task<int> SoftDelete<T>(this ISqlSugarClient Db, Expression<Func<T, bool>> wherexp, int DeleterId = 0) where T : ModelBase, new()
        {
            return await Trycatch(() => Db.Updateable<T>().Where(wherexp).SetColumns(a => new T()
            {
                IsDeleted = true,
                DeleteTime = DateTime.Now,
                DeleterId = DeleterId
            }).ExecuteCommandAsync());
        }

        #endregion 软删除

        /// <summary>
        /// try-catch包装
        /// </summary>
        /// <typeparam name="TResult"></typeparam>
        /// <param name="func"></param>
        /// <returns></returns>
        /// <exception cref="RepositoryException"></exception>
        public static async Task<TResult> Trycatch<TResult>(Func<Task<TResult>> func)
        {
            try
            {
                return await func();
            }
            catch (Exception ex)
            {
                // 自定义异常处理，可以记录日志
                throw new RepositoryException("数据库操作发生错误", ex);
            }
        }

        /// <summary>
        /// 事务操作
        /// </summary>
        /// <param name="actions"></param>
        /// <returns></returns>
        public static async Task ExecuteTransaction(this ISqlSugarClient Db, params Func<Task>[] actions)
        {
            if (actions == null || actions.Length == 0)
            {
                throw new ArgumentException("必须提供至少一个事务操作");
            }
            await Db.Ado.BeginTranAsync();
            try
            {
                // 并行执行所有事务操作
                await Task.WhenAll(actions.Select(action => action()));

                // 提交事务
                await Db.Ado.CommitTranAsync();
            }
            catch (Exception ex)
            {
                // 如果出现异常，回滚事务
                await Db.Ado.RollbackTranAsync();
                Console.WriteLine($"事务失败: {ex.Message}");
                throw;
            }
        }
    }
}